package com.test08.excelpoi06;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author wang
 * 1.这里默认顺序就是flag的顺序，
 * 2.如果需要修改顺序，需要先修改flag中内容，然后在修改FieldEntity中get()和set()
 */
public class ExecHander {
	private static String[] flag = null;
	private static String[][] datas = null;
	public static void main(String[] args) throws Exception {
		exec(new File("D:\\1.xlsx"),5,2,1,"日期,发货人,电话,地址,收货人,电话,地址,货物名称,件数,单号,受理人,备注");
		String join = StringUtils.join(flag, ",");
		System.out.println(join);
	}
	
	/**
	 * 使用了XSSFWorkbook只能处理xlsx文件
	 * @param src
	 * @param beginCol 开始的列
	 * @param beginRow 开始的行
	 * @param flags 
	 * @throws Exception
	 */
	public static void exec(File src,int beginCol,int beginRow,int writeSheetNum, String area) throws Exception{
		if (src.getName().endsWith(".xls")) {
			ExecHanderXls.exec(src, beginCol, beginRow, writeSheetNum, area);
			return;
		}
		setParam(area);
		
		// 得到一个可写的副本及sheet
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(src));
		
		//得到内容集合
		ArrayList<String[]> fileldEntities = read(xssfWorkbook, beginCol, beginRow);
		
		//把list写到某个sheet中
		if (xssfWorkbook.getSheet("分析结果")!=null) {
			xssfWorkbook.removeSheetAt(xssfWorkbook.getSheetIndex("分析结果"));
		}
		write(xssfWorkbook.createSheet("分析结果"), fileldEntities);
		
		//释放内存
		xssfWorkbook.write(new FileOutputStream(src));
		xssfWorkbook.close();
	}
	
	public static void setParam(String area){
		flag = area.split(",");
	}
	
	/**
	 * 封装内容
	 * @param xssfWorkbook
	 * @param beginCol
	 * @param beginRow
	 * @return
	 */
	public static ArrayList<String[]> read(XSSFWorkbook xssfWorkbook,int beginCol,int beginRow){
		XSSFSheet sheet0 = xssfWorkbook.getSheetAt(0);
		
		//获得总行数
		int sumRow = sheet0.getPhysicalNumberOfRows();
		
		//解析表格中内容，获得集合
		String content = null;
		ArrayList<String[]> fileldEntities = new ArrayList<>();
		for (int i = beginRow; i < sumRow; i++) {
			content = sheet0.getRow(i).getCell(beginCol).getStringCellValue().trim();
			// 不是需要的内容，就跳过
			if (!isOk(content)) {
				continue;
			}
			fileldEntities.add(convert(content));
		}
		
		return fileldEntities;
	}
	/**
	 * 判断是否是需要的元素格
	 * @param src
	 * @return
	 */
	public static boolean isOk(String src){
		for (String string : flag) {
			if (!StringUtils.contains(src, string)) {
				return false;
			}
		}
		return true;
	}
	/**
	 * 解析每个单元格，封装
	 * @param src
	 * @return
	 */
	public static String[] convert(String src){
		String[] data = new String[flag.length];
		String tmp = "";
		for (int i = 0; i < flag.length; i++) {
			if (i==flag.length-1) {
				tmp = StringUtils.substringAfter(src, flag[i]);
			}else{
				//取得两个关键字之间的值
				tmp = StringUtils.substringBetween(src, flag[i], flag[i+1]);
				//把源字符串给切割掉已经获得的
				src = StringUtils.substringAfter(src, tmp);
			}
			
			//获得的值进行切割处理
			if (StringUtils.startsWith(tmp, ":")) {
				tmp = StringUtils.substringAfter(tmp, ":");
			}
			
			if (StringUtils.startsWith(tmp, "：")) {
				tmp = StringUtils.substringAfter(tmp, "：");
			}
			data[i] = tmp.trim();
		}
		return data;
	}
	/**
	 * 把内容到sheet中
	 * @param hssfSheet
	 * @param fileldEntities
	 * @throws Exception
	 */
	public static void write(XSSFSheet hssfSheet,List<String[]> fileldEntities) throws Exception{
		XSSFRow hssfRow = null;
		
		
		//写入表头
		hssfRow = hssfSheet.createRow(0);
		hssfRow.createCell(0).setCellValue("计数");
		for (int i = 0; i < flag.length; i++) {
			hssfRow.createCell(i+1).setCellValue(flag[i]);
		}
		
		//写入结果
		for (int i = 0; i < fileldEntities.size(); i++) {
			//计数	
			hssfRow = hssfSheet.createRow(i+1);
			hssfRow.createCell(0).setCellValue(i+1);
			
			for (int j = 0; j < flag.length; j++) {
				hssfRow.createCell(j+1).setCellValue(fileldEntities.get(i)[j]);
			}
		}
	}
}
